﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Runtime.Serialization;
using System.Web;
using UniversityManagementSystem.DAL;

namespace UniversityManagementSystem.Gateway
{
    [Serializable]
    public class DepartmentGateway : GatewayConnection
    {
        private string commandString = null;
        [NonSerialized]
        private SqlDataReader getData = null;

        public string SaveNewDepartment(Department department)
        {
            int doneInserting = 0;
            string resultMessage = "";
            try
            {
                GetSqlConn.Open();
                commandString = string.Format("INSERT INTO t_Department(dept_code, dept_name) VALUES (@deptCode,@deptName)");
                GetSqlCommand.CommandText = commandString;
                GetSqlCommand.Parameters.Clear();
                GetSqlCommand.Parameters.Add("@deptCode", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@deptCode"].Value = department.DepartmentCode;
                GetSqlCommand.Parameters.Add("@deptName", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@deptName"].Value = department.DepartmentName;
                doneInserting = GetSqlCommand.ExecuteNonQuery();
                if (doneInserting > 0)
                {
                    resultMessage = "Department has been added successfully";
                }
                else
                {
                    resultMessage = "Department cannot be saved,Please try again later";
                }
            }
            catch (Exception)
            {
                resultMessage = "Error Occured.";
            }
            finally
            {
                if ((GetSqlConn != null) && (GetSqlConn.State == ConnectionState.Open))
                    GetSqlConn.Close();
            }
            return resultMessage;
        }

        public bool CheckDepartment(Department department)
        {
            bool departmentExists = false;
            try
            {
                GetSqlConn.Open();
                commandString = string.Format("SELECT * FROM t_Department WHERE dept_code = @deptCode OR dept_name = @deptName");
                GetSqlCommand.CommandText = commandString;
                GetSqlCommand.Parameters.Clear();
                GetSqlCommand.Parameters.Add("@deptCode", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@deptCode"].Value = department.DepartmentCode;
                GetSqlCommand.Parameters.Add("@deptName", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@deptName"].Value = department.DepartmentName;
                getData = GetSqlCommand.ExecuteReader();
                if(getData.Read())
                {
                    departmentExists = true;
                }
                GetSqlConn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if ((GetSqlConn != null) && (GetSqlConn.State == ConnectionState.Open))
                GetSqlConn.Close();
            }
            return departmentExists;
        }

        public List<Department> GetAllDepartments()
        {
            List<Department> allDepartment = new List<Department>();
            
            try
            {
                GetSqlConn.Open();
                commandString = string.Format("SELECT * FROM t_Department");
                GetSqlCommand.CommandText = commandString;
                getData = GetSqlCommand.ExecuteReader();
                while (getData.Read())
                {
                    Department department = new Department();
                    department.DepartmentCode = getData["dept_code"].ToString();
                    department.DepartmentName = getData["dept_name"].ToString();
                    allDepartment.Add(department);
                }
                GetSqlConn.Close();
            }
            catch (Exception exception)
            {
                throw exception;
            }
            finally
            {
                if ((GetSqlConn != null) && (GetSqlConn.State == ConnectionState.Open))
                GetSqlConn.Close();
            }
            return allDepartment;
        }

        public List<string> GetAllBuildings()
        {
            List<string> allBuildings = new List<string>();
            try
            {
                GetSqlConn.Open();
                commandString = string.Format("SELECT DISTINCT building_name FROM t_buildings");
                GetSqlCommand.CommandText = commandString;
                getData = GetSqlCommand.ExecuteReader();
                while (getData.Read())
                {
                    allBuildings.Add(getData["building_name"].ToString());
                }
                GetSqlConn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if ((GetSqlConn != null) && (GetSqlConn.State == ConnectionState.Open))
                    GetSqlConn.Close();
            }
            return allBuildings;
        }

        public List<string> GetAllRooms(string buildingName)
        {
            List<string> getAllRooms = new List<string>();
            try
            {
                GetSqlConn.Open();
                commandString = string.Format("SELECT room_no FROM t_Building WHERE building_name = @buildingName");
                GetSqlCommand.CommandText = commandString;
                GetSqlCommand.Parameters.Clear();
                GetSqlCommand.Parameters.Add("@buildingName", SqlDbType.VarChar);
                GetSqlCommand.Parameters["@buildingName"].Value = buildingName;
                getData = GetSqlCommand.ExecuteReader();
                while (getData.Read())
                {
                    getAllRooms.Add(getData["room_no"].ToString());
                }
                GetSqlConn.Close();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if ((GetSqlConn != null) && (GetSqlConn.State == ConnectionState.Open))
                    GetSqlConn.Close();
            }
            return getAllRooms;
        }
    }
}